﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Newtonsoft.Json;
using System.IO;
using System.Configuration;
using System.Net;
using DatabaseAccess.DBUtility;
using System.Collections;
using System.Data;
using Newtonsoft.Json.Linq;

namespace OracleTableSync
{
   public class CommonHelper
   {
       public static readonly String pushPath = ConfigurationManager.AppSettings["PushPath"].ToString();//推送路径

       /// <summary>
       /// 获取字段的类型和值
       /// </summary>
       /// <param name="_key"></param>
       /// <param name="fieldType"></param>
       /// <param name="fieldVal"></param>
     public static  void GetFieldValAndType(String[] _key, ref FieldEntity.FieldTypeEnum fieldType, ref Object fieldVal)
       {
           fieldType = FieldEntity.FieldTypeEnum.Str;
           if (_key[1].Contains("NUMBER"))
           {
               fieldType = FieldEntity.FieldTypeEnum.Numer;
           }
           if (_key[1].Contains("DATE"))
           {
               fieldType = FieldEntity.FieldTypeEnum.DateTime;
               if (fieldVal != null && !String.IsNullOrWhiteSpace(fieldVal.ToString()))
               {
                   fieldVal = DateTime.Parse(fieldVal.ToString());
               }
           }
           if (_key[1].Contains("TIMESTAMP"))
           {
               fieldType = FieldEntity.FieldTypeEnum.Timestamp;
               if (fieldVal != null && !String.IsNullOrWhiteSpace(fieldVal.ToString()))
               {
                   fieldVal = DateTime.Parse(fieldVal.ToString());
               }
           }
           if (_key[1].Contains("CLOB"))
           {
               fieldType = FieldEntity.FieldTypeEnum.Clob;
           }

           if (fieldVal == null)
               fieldVal = DBNull.Value;
           if (fieldVal != null && String.IsNullOrWhiteSpace(fieldVal.ToString()))
           {
               fieldVal = DBNull.Value;
           }
       }

       /// <summary>
       /// 根据表列信息和表名生成对象
       /// </summary>
       /// <param name="colInfo"></param>
       /// <param name="tableName"></param>
       /// <returns></returns>
       public static String generateSelectSql(DataTable colInfo,String tableName)
       {
           String sql = "select ";
           foreach(DataRow dr in colInfo.Rows){
               if (dr["data_type"].ToString().Contains("TIMESTAMP"))
               {
                   sql += String.Format("to_char({0},'yyyy-mm-dd hh24:mi:ss:ff6') {0},", dr["column_name"].ToString());
               }
               else if (dr["data_type"].ToString().Contains("DATE"))
               {
                   sql += String.Format("to_char({0},'yyyy-mm-dd hh24:mi:ss') {0},", dr["column_name"].ToString());               
               }
               else
               {
                   sql += String.Format("{0},", dr["column_name"].ToString());
               }
           }
           sql = sql.TrimEnd(',');
           sql += String.Concat(" from ", tableName);
           return sql;
       }

       /// <summary>
       /// 根据数据生成对象
       /// </summary>
       /// <param name="dt"></param>
       /// <param name="tableName"></param>
       /// <returns></returns>
       public static String generateStrJSON(DataTable dt, DataTable colInfo)
       {
           List<Dictionary<String, Object>> re =new List<Dictionary<String, Object>>();
           ArrayList needRemoveCol=new ArrayList();
           foreach (DataColumn col in dt.Columns) {
               DataRow[] _col=colInfo.Select(String.Format("column_name='{0}'",col.ColumnName));
               if (_col.Length == 0)
               {
                   needRemoveCol.Add(col.ColumnName); 
               }
               else
               {
                   col.ColumnName = col.ColumnName + "@" + _col[0]["data_type"];               
               }
           }

           foreach (String colname in needRemoveCol) {
               dt.Columns.Remove(colname);
           }
            
           return JsonConvert.SerializeObject(dt);
       }

       /// <summary>
       /// 获取表的列信息
       /// </summary>
       /// <param name="tableName"></param>
       /// <returns></returns>
       public static DataTable getTableColInfo(String tableName) {
           return DatabaseHelper.Query_ReturnDatTable(string.Format(@"SELECT column_name,data_type FROM user_tab_columns WHERE table_name = upper('{0}') order by column_name", tableName));
       }

        /// <summary>
        /// 写入需要推送的记录到文本
        /// </summary>
        /// <param name="dt"></param>
       public static String writeJsonToFile(List<Dictionary<String, Object>> jsonFile,String fileName)
        {
            String jsonData = JsonConvert.SerializeObject(jsonFile);
            String fileName2 = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fffffff") + "_" + fileName + ".json";
            String fileFullName = pushPath + fileName2;
            // 创建一个新的JSON文件，并将jsonData写入其中
            File.WriteAllText(fileFullName, jsonData);
            return fileName2;
        }

       /// <summary>
       /// 存入json文件推送信息
       /// </summary>
       public static void savePushFileRecord(List<Dictionary<String, Object>> jsonFile,String fileName)
       {
           var excute = new ExcuteEntity()
                       {
                           TableName = "Sync_PushFileRecord",
                           ExcuteType = ExcuteEntity.ExcuteTypeEnum.insert,
                           ExcuteFileds = new List<FieldEntity>() { new FieldEntity("id", Guid.NewGuid().ToString(), FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("PushID", jsonFile[0]["pushID"].ToString(), FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("FileName", fileName, FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("JsonFile", JsonConvert.SerializeObject(jsonFile), FieldEntity.FieldTypeEnum.Clob),
                               new FieldEntity("CreateTime", DateTime.Now, FieldEntity.FieldTypeEnum.Timestamp),
                               new FieldEntity("LastChangeTime", DateTime.Now, FieldEntity.FieldTypeEnum.Timestamp)
                           }
                       };

           if (jsonFile[0].ContainsKey("tableTaskID"))
           {
               excute.ExcuteFileds.Add(new FieldEntity("tableTaskID", jsonFile[0]["tableTaskID"].ToString(), FieldEntity.FieldTypeEnum.Str));
           }
           DatabaseHelper.ExecuteDataByEntity(excute);
       }

       /// <summary>
       /// 存入json文件接收信息
       /// </summary>
       public static void saveReceiveFileRecord(List<Dictionary<String, Object>> jsonFile, String fileName)
       {
           var excute = new ExcuteEntity()
           {
               TableName = "Sync_ReceiveFileRecord",
               ExcuteType = ExcuteEntity.ExcuteTypeEnum.insert,
               ExcuteFileds = new List<FieldEntity>() { new FieldEntity("id", Guid.NewGuid().ToString(), FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("ReceiveID", jsonFile[0]["pushID"].ToString(), FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("FileName", fileName, FieldEntity.FieldTypeEnum.Str),
                               new FieldEntity("JsonFile", JsonConvert.SerializeObject(jsonFile), FieldEntity.FieldTypeEnum.Clob),
                               new FieldEntity("CreateTime", DateTime.Now, FieldEntity.FieldTypeEnum.Timestamp),
                               new FieldEntity("LastChangeTime", DateTime.Now, FieldEntity.FieldTypeEnum.Timestamp)
                           }
           };

           if (jsonFile[0].ContainsKey("tableTaskID"))
           {
               excute.ExcuteFileds.Add(new FieldEntity("tableTaskID", jsonFile[0]["tableTaskID"].ToString(), FieldEntity.FieldTypeEnum.Str));
           }
           DatabaseHelper.ExecuteDataByEntity(excute);
       }

       /// <summary>
       /// 移动文件到指定路径
       /// </summary>
       /// <param name="fi"></param>
       /// <param name="receiveFileBak"></param>
       public static void moveFile(FileInfo fi, String receiveFileBak)
       {
           //移动文件
           File.Delete(receiveFileBak + fi.Name);
           File.Move(fi.FullName, receiveFileBak + fi.Name);
       }

       /// <summary>
       /// 获取IP
       /// </summary>
       /// <returns></returns>
       public static String GetIP()
       {
           String GetIP = "";


           if (string.IsNullOrWhiteSpace(GetIP))
           {
               IPHostEntry HostIP;
               // //�I;:IP0@�
               IPAddress IPAdress;
               HostIP = Dns.GetHostByName(Dns.GetHostName());
               // //�,0;:�IP0@�
               IPAdress = new IPAddress(HostIP.AddressList[0].Address);
               // //�,0;:�IP0@�
               GetIP = IPAdress.ToString();
           }

           return GetIP;

       }

       /// <summary>
       /// 通过表名找到对应映射信息
       /// </summary>
       /// <param name="tableName"></param>
       /// <returns></returns>
       public static JObject getMapTabByTableName(List<JObject> tabMapTab, string tableName, string orgDb)
       {
           var thisTab = tabMapTab.Where(tab => tab["tableinfo"][orgDb].ToString().ToUpper() == tableName.ToUpper());
           if (thisTab == null || thisTab.Count() == 0)
           {
               return null;
           }

           return thisTab.ElementAt(0);
       }

       /// <summary>
       /// 获取数据同步目标字段
       /// </summary>
       /// <param name="tabMap"></param>
       /// <param name="orgDb"></param>
       /// <param name="toDb"></param>
       /// <param name="orgFiled"></param>
       /// <returns></returns>
       public static Dictionary<string, Object> getToField(JObject tabMap, string orgDb, string toDb, string orgFiled)
       {
           JArray fieldinfos = tabMap.Value<JArray>("fieldinfo");
           Dictionary<string, Object> result = new Dictionary<string, Object>();
           foreach (JObject singleField in fieldinfos)
           {
               if (orgFiled.ToUpper() != singleField[orgDb].ToString().ToUpper())
               {
                   continue;
               }

               Object toField = singleField[toDb];
               if (toField is JValue)
               {
                   result.Add(toDb, toField.ToString());
                   result.Add("mapType", "str");
                   return result;
               }
               if (toField is JObject)
               {
                   result.Add(toDb, toField);
                   result.Add("mapType", "attr");
                   return result;
               }
           }

           return null;
       }

       /// <summary>
       /// 保存属性信息,表字段转化为属性行数据
       /// </summary>
       /// <param name="attrs"></param>
       /// <param name="val"></param>
       public static void saveAttrsData(JObject attrs, String parentId,String val)
       {
           string tableName = attrs["attrtable"].ToString();
           string sql = @"declare 
v_count number;
begin
  select count(1) into v_count from {4} t where t.parentid='{0}' and t.attributename='{1}';
  if v_count=0 then
    insert into {4} t (ATTRIBUTEID,parentid,ATTRIBUTENAME,ATTRIBUTEVALUE,ATTRIBUTEDESC)
    values (sys_guid(),'{0}','{1}','{2}','{3}');
  else
      update {4} t set t.attributevalue='{2}' where t.attributeid IN (
      select t.attributeid from {4} t where t.parentid='{0}' and t.attributename='{1}');
  end if;
  commit;
end;";
           sql = String.Format(sql, parentId, attrs["attributename"].ToString(), val, attrs["attributedesc"].ToString(), tableName);

           DatabaseHelper.ExecuteSql(sql);
       }

       /// <summary>
       /// 判断两个新值是否相等
       /// 比如：{"CONTAINERNAME@VARCHAR":"JGJ001/1709006$$$$"}和{"CONTAINERNAME@VARCHAR2":"JGJ001/1709006$$$$"}应该是相等的,但是一个数据库是varchar，另一个是varchar2
       /// </summary>
       /// <param name="newValue1"></param>
       /// <param name="newValue2"></param>
       /// <returns></returns>
       public static Boolean checkEqualsNewValues(string newValue1, string newValue2) {

           if (newValue1 == newValue2) {
               return true;
           }

           JObject jObject1 = JObject.Parse(newValue1);
           JObject jObject2 = JObject.Parse(newValue2);

           string str1 = ""; string str2 = "";
           foreach (JProperty property in jObject1.Properties())
           {
               if (property.Name.Contains("@"))
               {
                   str1 += property.Name.Split('@')[0];
                   str1 += ":" + property.Value + ",";
               }
               else
               {
                   str1 += property.Name;
                   str1 += ":" + property.Value + ",";
               }
           }

           foreach (JProperty property in jObject2.Properties())
           {
               if (property.Name.Contains("@"))
               {
                   str2 += property.Name.Split('@')[0];
                   str2 += ":" + property.Value + ",";
               }
               else
               {
                   str2 += property.Name;
                   str2 += ":" + property.Value + ",";
               }
           }

           return str1 == str2;
       }
    }
}
